CREATE TRIGGER MarkMerchant
    ON history_order
    AFTER UPDATE
    AS
BEGIN
    DECLARE @AvgMark1 DECIMAL(3,2);  -- 低于0.5的订单平均分
    DECLARE @AvgMark2 DECIMAL(3,2);
    DECLARE @AvgMark3 DECIMAL(3,2);  -- 高于4.5的订单平均分
    DECLARE @AvgMark DECIMAL(3,2);  -- 最终评分
    DECLARE @num1 INT;  -- 低于0.5的订单数量
    DECLARE @num2 INT;
    DECLARE @num3 INT;  -- 高于4.5的订单数量
    DECLARE @MerchantPhoneNumber VARCHAR(15);
    DECLARE @Mark INT;

    SELECT @MerchantPhoneNumber = merchant_phone_number FROM inserted;
    SELECT @Mark = merchant_order_grade FROM inserted;

    SELECT @AvgMark1 = AVG(merchant_order_grade), @num1 = COUNT(merchant_phone_number)
    FROM history_order
    WHERE merchant_order_grade < 0.5 AND merchant_order_grade IS NOT NULL AND merchant_phone_number = @MerchantPhoneNumber;

    SELECT @AvgMark2 = AVG(merchant_order_grade), @num2 = COUNT(merchant_phone_number)
    FROM history_order
    WHERE merchant_order_grade >= 0.5 AND merchant_order_grade <= 4.5 AND merchant_order_grade IS NOT NULL AND merchant_phone_number = @MerchantPhoneNumber;

    SELECT @AvgMark3 = AVG(merchant_order_grade), @num3 = COUNT(merchant_phone_number)
    FROM history_order
    WHERE merchant_order_grade > 4.5 AND merchant_order_grade IS NOT NULL AND merchant_phone_number = @MerchantPhoneNumber;

    -- 评分值域约束
    IF @Mark < 0 OR @Mark > 5 BEGIN
        ROLLBACK;
    END
    ELSE BEGIN
        IF (@num1 = 0) SELECT @AvgMark1 = 0;
        IF (@num2 = 0) SELECT @AvgMark2 = 0;
        IF (@num3 = 0) SELECT @AvgMark3 = 0;

        IF (@num1 + @num2 + @num3 < 50) BEGIN  -- 总数量很少时，适当降低低档评分权重
        UPDATE merchant
        SET shop_grade = (@AvgMark1 * @num1 * 0.5 + @AvgMark2 * @num2 + @AvgMark3 * @num3) / (@num1 * 0.5 + @num2 + @num3)
        WHERE merchant_phone_number = @MerchantPhoneNumber;
        END
        ELSE IF (@num1 + @num2 + @num3 < 5000) BEGIN  -- 总数量适中时，适当增加中间档评分权重
        UPDATE merchant
        SET shop_grade = (@AvgMark1 * @num1 * 0.75 + @AvgMark2 * @num2 + @AvgMark3 * @num3 * 0.8) / (@num1 * 0.75 + @num2 + @num3 * 0.8)
        WHERE merchant_phone_number = @MerchantPhoneNumber;
        END
        ELSE BEGIN  -- 总数量很多时，则不加干预
        UPDATE merchant
        SET shop_grade = (@AvgMark1 * @num1 + @AvgMark2 * @num2 + @AvgMark3 * @num3) / (@num1 + @num2 + @num3)
        WHERE merchant_phone_number = @MerchantPhoneNumber;
        END
    END;
END;
go

